﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.IO.IsolatedStorage;
using wzSqlAdmin.nwDataBase;
using System.Collections;
using System.Collections.ObjectModel;
using wzSqlAdmin.Util;
using System.Windows.Markup;
using System.Windows.Data;
using SL4PopupMenu;
using System.Globalization;
using System.Threading;

namespace wzSqlAdmin
{
    public partial class MainPage : UserControl
    {
        // Web Service for data
        private nwDataBase.InwDataBaseClient db;
        // List of tables returned from Web Services
        ObservableCollection<nwDataBase.DataTableInfo> _tables;
        // Parameters for connection
        private Dictionary<string, string> parametri = new Dictionary<string, string>();

        // Database Items in Accordion menu
        private AccordionItem AccSrv;
        private AccordionItem AccTab;
        private AccordionItem AccUser;
        private AccordionItem AccView;
        private AccordionItem AccProcs;
        private AccordionItem AccFuncs;

        // Pages
        private pagine.Connessione winConn = new pagine.Connessione();
        // When Table Selected
        private string _TableSel;
        // Type of object requested
        private string tiporichiesto;
        private string host="";
        IEnumerable _lookup;
        public MainPage()
        {
            InitializeComponent();
            // Template for data
            TemplateManager.LoadTemplate("DataGrid/Content.zip");
            // Link to correct web service
             host = Application.Current.Host.Source.AbsoluteUri;
            host = host.Substring(0, host.LastIndexOf("/"));
            host = host.Substring(0, host.LastIndexOf("/"));
            db = new nwDataBase.InwDataBaseClient("BasicHttpBinding_InwDataBase", host + "/nwDataBase.svc");

            // Events for data retrieve
            db.getSqlCompleted += new EventHandler<getSqlCompletedEventArgs>(RecDati);
            db.getSchemaCompleted += new EventHandler<getSchemaCompletedEventArgs>(RecSchema);
            db.ConnettiCompleted += new EventHandler<ConnettiCompletedEventArgs>(TestConnCompleto);
            winConn.Closed += new EventHandler(ConnWinChiusa);
            db.ListObjectsCompleted += new EventHandler<ListObjectsCompletedEventArgs>(GetObjects);
            db.ListDataBasesCompleted += new EventHandler<ListDataBasesCompletedEventArgs>(GetDataBases);

            //Init
            ExQuery.Visibility = Visibility.Collapsed;
            if (Application.Current.IsRunningOutOfBrowser)
            {
                btnInst.Visibility = Visibility.Collapsed;
                App.Current.CheckAndDownloadUpdateAsync();
                App.Current.CheckAndDownloadUpdateCompleted += new CheckAndDownloadUpdateCompletedEventHandler(UpdateAvail);
            }

        }
        protected void UpdateAvail(object sender, CheckAndDownloadUpdateCompletedEventArgs e)
        {
            if (e.UpdateAvailable)
            {
                MessageBox.Show("Update available.\nPlease restart the application to apply");
            }
        }
        #region Draw
        private Color FromHex(string Hex)
        {
            byte a = System.Convert.ToByte(Hex.Substring(0, 2), 16);
            byte r = System.Convert.ToByte(Hex.Substring(2, 2), 16);
            byte g = System.Convert.ToByte(Hex.Substring(4, 2), 16);
            byte b = System.Convert.ToByte(Hex.Substring(6, 2), 16);
            return Color.FromArgb(a, r, g, b);
        }
        private StackPanel CreaContentItem()
        {

            StackPanel g = new StackPanel();
            g.Margin = new Thickness(8, 0, 8, 8);
            return g;
        }
        private ScrollViewer CreaContentScroll()
        {
            ScrollViewer s = new ScrollViewer();
            s.VerticalScrollBarVisibility = ScrollBarVisibility.Auto;

            return s;
        }
        private Border CreaContentBorder()
        {

            Border b = new Border();
            b.BorderThickness = new Thickness(1, 0, 1, 1);
            b.CornerRadius = new CornerRadius(0, 0, 3, 3);
            b.BorderBrush = new SolidColorBrush(FromHex("FF242424"));
            b.Margin = new Thickness(8, 0, 8, 8);
            LinearGradientBrush lgb = new LinearGradientBrush();
            lgb.EndPoint = new Point(0.5, 1);
            lgb.StartPoint = new Point(0.5, 0);
            lgb.Opacity = 0.145;
            GradientStop lgbs = new GradientStop();
            lgbs.Color = new SolidColorBrush(FromHex("FF7E7E7E")).Color;
            lgb.GradientStops.Add(lgbs);
            lgbs = new GradientStop();
            lgbs.Color = new SolidColorBrush(FromHex("FFFFFFFF")).Color;
            lgbs.Offset = 1;
            lgb.GradientStops.Add(lgbs);
            b.Background = lgb;

            return b;
        }
        private AccordionItem CreaAccItem(string Titolo)
        {
            AccordionItem ai = new AccordionItem();
            ai.Header = Titolo;
            ai.AccordionButtonStyle = (Style)Application.Current.Resources["AccordionButtonStyleLawBot_2"];
            ai.BorderBrush = new SolidColorBrush(FromHex("FF00102E"));
            ai.FontSize = 13.333;
            ai.Margin = new Thickness(0, 1, 0, 0);
            ai.HorizontalContentAlignment = HorizontalAlignment.Stretch;
            ai.VerticalAlignment = VerticalAlignment.Stretch;

            ai.Foreground = new SolidColorBrush(FromHex("FFFFFFFF"));
            ai.Effect = (System.Windows.Media.Effects.Effect)Application.Current.Resources["AccordioItemDropShadow"];
            ai.Margin = new Thickness(0, 0, 0, 4);
            Border b = new Border();
            b.BorderThickness = new Thickness(1, 0, 1, 1);
            b.CornerRadius = new CornerRadius(0, 0, 3, 3);
            b.BorderBrush = new SolidColorBrush(FromHex("FF242424"));
            b.Margin = new Thickness(8, 0, 8, 8);
            LinearGradientBrush lgb = new LinearGradientBrush();
            lgb.EndPoint = new Point(0.5, 1);
            lgb.StartPoint = new Point(0.5, 0);
            lgb.Opacity = 0.145;
            GradientStop lgbs = new GradientStop();
            lgbs.Color = new SolidColorBrush(FromHex("FF7E7E7E")).Color;
            lgb.GradientStops.Add(lgbs);
            lgbs = new GradientStop();
            lgbs.Color = new SolidColorBrush(FromHex("FFFFFFFF")).Color;
            lgbs.Offset = 1;
            lgb.GradientStops.Add(lgbs);
            b.Background = lgb;
            Grid g = new Grid();
            g.Margin = new Thickness(8, 0, 8, 8);
            b.Child = g;
            ai.Content = b;
            return ai;
        }
        private PopupMenuItem MItem(string name, string text, object k, RoutedEventHandler target)
        {
            PopupMenuItem mi = new PopupMenuItem();
            mi.Name = name;
            TextBlock tx = new TextBlock();
            tx.Text = text;

            //mi.ContentRight = tx;
            mi.Header = tx;
            if (k != null)
            {
                Key key = (Key)k;
                mi.ShortcutKeyModifier1 = ModifierKeys.Alt;
                mi.ShortcutKeyModifier2 = ModifierKeys.None;
                mi.ShortcutKey = key;
            }

            mi.Click += new RoutedEventHandler(target);
            return mi;

        }
        private void CambioBr(object sender, MouseEventArgs e)
        {
            Border br = (Border)sender;
            br.Background = new SolidColorBrush(FromHex("FF908A8A"));
        }
        private void CambioBrF(object sender, MouseEventArgs e)
        {
            Border br = (Border)sender;


            br.Background = new SolidColorBrush(FromHex("FFDCD9D9"));

        }
        private Border CreaBorderMnu(string name, string text)
        {
            Border br = new Border();
            br.BorderBrush = new SolidColorBrush(FromHex("FF242424"));
            br.BorderThickness = new Thickness(1);

            br.Background = new SolidColorBrush(FromHex("FFDCD9D9"));
            br.Height = 23;
            br.Padding = new Thickness(0);
            br.Margin = new Thickness(0);
            br.CornerRadius = new CornerRadius(3);
            br.HorizontalAlignment = HorizontalAlignment.Stretch;
            br.MouseEnter += new MouseEventHandler(CambioBr);
            br.MouseLeave += new MouseEventHandler(CambioBrF);

            //br.Width = 300;
            //br.HorizontalAlignment = HorizontalAlignment.Left;
            TextBlock tb = new TextBlock();
            tb.Text = text;
            tb.Foreground = new SolidColorBrush(FromHex("FF565555"));
            tb.FontWeight = FontWeights.SemiBold;
            tb.VerticalAlignment = VerticalAlignment.Center;
            tb.HorizontalAlignment = HorizontalAlignment.Center;
            tb.Name = name;
            br.Child = tb;
            return br;
        }
        #endregion
        #region Navigate
        private void ContentFrame_Navigated(object sender, System.Windows.Navigation.NavigationEventArgs e)
        {

        }
        private void ContentFrame_NavigationFailed(object sender, System.Windows.Navigation.NavigationFailedEventArgs e)
        {

        }
        #endregion
        #region Connection
        // Button Click for Connect
        private void Esegui(object sender, RoutedEventArgs e)
        {
            winConn.Show();
        }
        //Exit Connect Windows
        void ConnWinChiusa(object sender, EventArgs e)
        {
            if (winConn.DialogResult != null)
            {
                if ((bool)winConn.DialogResult)
                {
                    // Parameters to connect
                    // Type: Server Type:
                    //      SQL= SQL Server
                    //      MySql= MySql Server
                    //      Oracle = Oracle Server (no implemented yet)
                    // Server: Database Server
                    // DB: Database Name (not used in first connection)
                    // UID/Pwd: User - password
                    // trusted: For Sql Server: trusted connection (true/false)
                    // port: For MySql Server: server port
                    parametri.Clear();
                    if (winConn.ConnType.SelectedIndex == 0)
                    {
                        parametri.Add("Type", "SQL");
                        parametri.Add("Server", winConn.SqlServerChoice.Text);
                        parametri.Add("DB", "");
                        parametri.Add("UID", winConn.SqlUser.Text);
                        parametri.Add("Pwd", winConn.SqlPwd.Password);
                        parametri.Add("trusted", winConn.SqlTrusted.IsChecked.ToString());
                        parametri.Add("port", "0");
                    }
                    else
                    {
                        parametri.Add("Type", "MySQL");
                        parametri.Add("Server", winConn.mySqlServerChoice.Text);
                        parametri.Add("DB", "");
                        parametri.Add("UID", winConn.mySqlUser.Text);
                        parametri.Add("Pwd", winConn.mySqlPwd.Password);
                        parametri.Add("trusted", false.ToString());
                        parametri.Add("port", winConn.mySqlPort.Text.ToString());
                    }
                    // Test Connection
                    db.ConnettiAsync(parametri);
                }
            }
        }
        // End Test Connection
        public void TestConnCompleto(object sender, ConnettiCompletedEventArgs e)
        {
            if (e.Error == null)
            {
                if (e.ServiceError != null)
                {
                    MessageBox.Show(e.ServiceError.Message + " " + host);
                }
                else
                {
                    // Save connection data in StateBag
                    Impostazioni i = new Impostazioni();
                    i.setImpostazione((string)parametri["Type"], (string)parametri["Server"], (string)parametri["UID"], (string)parametri["Pwd"], bool.Parse(parametri["trusted"]), int.Parse(parametri["port"]));
                    // Get Databases List 
                    db.ListDataBasesAsync(parametri);
                }
            }
            else
            {
                MessageBox.Show(e.Error.Message + " " + host + " " + e.Error.StackTrace.ToString() + " " + e.Error.Data.ToString());
                if(e.Error.InnerException!=null)
                MessageBox.Show(e.Error.InnerException.Message + " " + e.Error.InnerException.ToString());
            }
        }
        #endregion
        #region DataBases
        public void GetDataBases(object sender, ListDataBasesCompletedEventArgs e)
        {
            if (e.Error == null)
            {
                // Create Accordion List of databases
                Acc.Height = LayoutRoot.ActualHeight - 50;
                Dictionary<string, string> d = (Dictionary<string, string>)e.Result;
                // If First Time, create Accordion Item
                if (AccSrv == null)
                {
                    AccSrv = CreaAccItem("Server " + parametri["Server"]);
                    AccSrv.MinHeight = 10;
                    Acc.Items.Add(AccSrv);
                }
                StackPanel g = CreaContentItem();
                Border b = CreaContentBorder();
                ScrollViewer s = CreaContentScroll();
                b.Child = g;
                s.Content = b;
                AccSrv.Content = s;
                foreach (KeyValuePair<string, string> kvp in d)
                {
                    // Add for each database Button with event to load data
                    Button h = new Button();
                    h.CommandParameter = kvp.Key;
                    h.Content = kvp.Key;
                    h.Click += new RoutedEventHandler(SceltaDB);
                    g.Children.Add(h);
                }
                Acc.UpdateLayout();
            }
            else
            {
                MessageBox.Show(e.Error.Message + " " + host);
            }
        }
        #endregion
        #region DataBase Items
        private void SceltaDB(object sender, RoutedEventArgs e)
        {
            // Get Database name and put in parameters 
            Button b = (Button)sender;
            parametri["DB"] = b.CommandParameter.ToString();
            // Load Objects
            db.ListObjectsAsync(parametri);
            // Create and Empty Accordion DataBase Objects Items
            if (AccTab != null)
            {
                Acc.Items.Remove(AccTab);
                AccTab = null;
            }
            if (AccProcs != null)
            {
                Acc.Items.Remove(AccProcs);
                AccProcs = null;
            }
            if (AccFuncs != null)
            {
                Acc.Items.Remove(AccFuncs);
                AccFuncs = null;
            }
            if (AccUser != null)
            {
                Acc.Items.Remove(AccUser);
                AccUser = null;

            }
            if (AccView != null)
            {
                Acc.Items.Remove(AccView);
                AccView = null;

            }

        }
        public void GetObjects(object sender, ListObjectsCompletedEventArgs e)
        {
            if (e.Error == null)
            {
                Dictionary<string, string> d = (Dictionary<string, string>)e.Result;
                // Load on Accordion Items the objects retrieved
                if (AccTab == null)
                {
                    AccTab = CreaAccItem(parametri["DB"] + ":Tabelle");
                    Acc.Items.Add(AccTab);
                }
                StackPanel spTables = CreaContentItem();
                Border b = CreaContentBorder();
                ScrollViewer s = CreaContentScroll();
                b.Child = spTables;
                s.Content = b;
                AccTab.Content = s;
                if (AccView == null)
                {
                    AccView = CreaAccItem(parametri["DB"] + ":Viste");
                    Acc.Items.Add(AccView);
                }
                StackPanel spView = CreaContentItem();
                b = CreaContentBorder();
                s = CreaContentScroll();
                b.Child = spView;
                s.Content = b;
                AccView.Content = s;
                if (AccProcs == null)
                {
                    AccProcs = CreaAccItem(parametri["DB"] + ":Procedure");
                    Acc.Items.Add(AccProcs);
                }
                StackPanel spProcs = CreaContentItem();
                b = CreaContentBorder();
                s = CreaContentScroll();
                b.Child = spProcs;
                s.Content = b;
                AccProcs.Content = s;
               
                if (AccUser == null)
                {
                    AccUser = CreaAccItem(parametri["DB"] + ":Utenti");
                    Acc.Items.Add(AccUser);
                }
                StackPanel spUser = CreaContentItem();
                b = CreaContentBorder();
                s = CreaContentScroll();
                b.Child = spUser;
                s.Content = b;
                AccUser.Content = s;
                bool fatto = false;

                foreach (KeyValuePair<string, string> kvp in d)
                {
                    fatto = false;
                    // For each Object create a context menu with object property
                    if (kvp.Value == "user")
                    {
                        // User:
                        // View Property
                        // Change pwd (not implemented yet)
                        // Add permission (not implemented yet)
                        // Create User (not implemented yet)
                        spUser.Children.Add(CreaBorderMnu(kvp.Key, kvp.Key));

                        PopupMenu m = new PopupMenu();
                        m.HoverElements = kvp.Key;
                        m.EnableShadowEffect = true;
                        m.Items.Add(MItem("U" + kvp.Key, "Property", null, mnuTBSchema));
                        spUser.Children.Add(m);
                        fatto = true;
                    }

                    if (kvp.Value == "base table")
                    {
                        spTables.Children.Add(CreaBorderMnu(kvp.Key, kvp.Key));
                        // Tables:
                        // View Data
                        // Change Data (not implemented yet)
                        // View Columns, Identity, Guid, Files, Indexes, Constrains, Ext. and View ref. 
                        // Add/Delete/Modify for every object (not implemented yet)
                        PopupMenu m = new PopupMenu();
                        m.HoverElements = kvp.Key;
                        m.EnableShadowEffect = true;
                        m.Items.Add(MItem("D" + kvp.Key, "Data", null, mnuTBData));
                        m.Items.Add(MItem("C" + kvp.Key, "Columns", null, mnuTBSchema));
                        m.Items.Add(MItem("I" + kvp.Key, "Identity", null, mnuTBSchema));
                        m.Items.Add(MItem("G" + kvp.Key, "Guid", null, mnuTBSchema));
                        m.Items.Add(MItem("F" + kvp.Key, "Files", null, mnuTBSchema));
                        m.Items.Add(MItem("X" + kvp.Key, "Indexes", null, mnuTBSchema));
                        m.Items.Add(MItem("S" + kvp.Key, "Constrains", null, mnuTBSchema));
                        m.Items.Add(MItem("E" + kvp.Key, "Ref.External", null, mnuTBSchema));
                        m.Items.Add(MItem("V" + kvp.Key, "Ref.View", null, mnuTBSchema));
                        spTables.Children.Add(m);

                        fatto = true;
                    }

                    if (kvp.Value.ToString() == "view")
                    {
                        // Tables:
                        // View Data
                        // Change Data (not implemented yet)
                        // View Columns, Identity, Guid  
                        // Add/Delete/Modify for every object (not implemented yet)
                        spView.Children.Add(CreaBorderMnu(kvp.Key, kvp.Key));

                        PopupMenu m = new PopupMenu();
                        m.HoverElements = kvp.Key;
                        m.EnableShadowEffect = true;
                        m.Items.Add(MItem("D" + kvp.Key, "Data", null, mnuTBData));
                        m.Items.Add(MItem("C" + kvp.Key, "Columns", null, mnuTBSchema));
                        m.Items.Add(MItem("I" + kvp.Key, "Identity", null, mnuTBSchema));
                        m.Items.Add(MItem("G" + kvp.Key, "Guid", null, mnuTBSchema));

                        spView.Children.Add(m);

                        fatto = true;
                    }

                    if (kvp.Value.ToString() == "procedure")
                    {
                        // Tables:
                        // View Procedure
                        // View Parameters
                        // Exec Procedure (not implemented yet)
                        // Add/Delete/Modify for every object (not implemented yet)

                        spProcs.Children.Add(CreaBorderMnu(kvp.Key, kvp.Key));

                        PopupMenu m = new PopupMenu();
                        m.HoverElements = kvp.Key;
                        m.EnableShadowEffect = true;
                        //m.Items.Add(MItem("Y" + kvp.Key, "EXEC", null, mnuTBSchema));
                        m.Items.Add(MItem("P" + kvp.Key, "Procedure", null, mnuTBSchema));
                        m.Items.Add(MItem("R" + kvp.Key, "Parameters", null, mnuTBSchema));
                        spProcs.Children.Add(m);

                        fatto = true;
                    }


                    if (!fatto)
                    {
                        //Button h = new Button();
                        //h.Content = kvp.Value;
                        //spProcs.Children.Add(h);
                        //fatto = true;
                    }
                }

                AccTab.IsSelected = true;
                Acc.UpdateLayout();

            }
            else
            {
                MessageBox.Show(e.Error.Message + " " + host);
            }

        }
        #endregion
        #region Table Data
        private void mnuTBData(object sender, RoutedEventArgs e)
        {
            // Get Table name (Format D + Table Name)
            PopupMenuItem mi = (PopupMenuItem)sender;
            string table = mi.Name.Substring(1);
            _TableSel = table;
            // Get Table Data
            db.getSqlAsync(parametri, "select * from " + table, 1, 100);
            Busy.IsBusy = true;
            Acc.IsEnabled = false;
        }
        private void RecDati(object sender, getSqlCompletedEventArgs e)
        {
            Busy.IsBusy = false;

            Acc.IsEnabled = true;
            if (e.Error == null)
            {
                if (e.ServiceError == null)
                {
                    // Table Data : Table 0 in array
                    _tables = e.Result.Tables;
                    IEnumerable list = DynamicDataBuilder.GetDataList(e.Result, 0);
                    if (e.UserState as string == "Lookup")
                        _lookup = list;
                    else
                    {
                        
                        // View result in page with dataGrid
                        pagine.Risultati r = new pagine.Risultati();
                        r.ris.Columns.Clear();
                        r.Table = _TableSel;
                        r.parametri = parametri;
                        r.ris.ItemsSource = list;
                        r.cboPage.Items.Clear();
                        r.cboPage.Items.Add(10);
                        r.cboPage.Items.Add(50);
                        r.cboPage.Items.Add(100);
                        r.cboPage.Items.Add(200);
                        r.cboPage.Items.Add(500);
                        r.cboPage.Items.Add(1000);
                        r.cboPage.SelectedValue = 100;

                        r.lbTot.Content = ((e.TotRecors / 100) + 1);

                        
                        // Set PageSize
                        
                        
                        r.cboAttPage.Items.Clear();
                        for (int i = 1; i <= ((e.TotRecors / 100)+1); i++)
                        {
                            r.cboAttPage.Items.Add(i);
                        }
                        r.cboAttPage.SelectedIndex = 0;

                        // Create DataGrid with formatted columns
                        if (e.Result.Tables.Count > 0)
                        {
                            foreach (nwDataBase.DataColumnInfo column in e.Result.Tables[0].Columns)
                            {
                                if (column.DisplayIndex != -1)
                                {
                                    DataGridColumn col;
                                    DataTemplate dt;
                                    if (column.DataTypeName == typeof(bool).FullName)
                                    {
                                        DataGridCheckBoxColumn checkBoxColumn = new DataGridCheckBoxColumn();
                                        checkBoxColumn.Binding = new Binding(column.ColumnName);
                                        col = checkBoxColumn;
                                    }
                                    else if (column.DataTypeName == typeof(DateTime).FullName)
                                    {
                                        col = null;
                                        try
                                        {
                                            DataGridTemplateColumn templateColumn = new DataGridTemplateColumn();
                                            string temp = TemplateManager.DataTemplates["DateTimeCellTemplate"];
                                            temp = temp.Replace("@HorizontalAlignment@", HorizontalAlignment.Left.ToString());
                                            temp = temp.Replace("@Text@", column.ColumnName);
                                            temp = temp.Replace("@DateTimeFormat@", "MM/dd/yyyy");

                                            dt = XamlReader.Load(temp) as DataTemplate;
                                            templateColumn.CellTemplate = dt;

                                            DataTemplate t = new DataTemplate();

                                            temp = TemplateManager.DataTemplates["DateTimeCellEditingTemplate"];
                                            temp = temp.Replace("@HorizontalAlignment@", HorizontalAlignment.Left.ToString());
                                            temp = temp.Replace("@SelectedDate@", column.ColumnName);

                                            dt = XamlReader.Load(temp) as DataTemplate;

                                            templateColumn.CellEditingTemplate = dt;
                                            col = templateColumn;
                                        }
                                        catch (Exception ex)
                                        {
                                            MessageBox.Show(ex.Message);
                                        }
                                    }
                                    else
                                    {
                                        DataGridTextColumn textColumn = new DataGridTextColumn();
                                        textColumn.Binding = new Binding(column.ColumnName);
                                        textColumn.Binding.ValidatesOnExceptions = true;
                                        col = textColumn;
                                    }

                                    col.IsReadOnly = column.IsReadOnly;

                                    //HyperlinkButton b = new HyperlinkButton();
                                    //b.Background = new SolidColorBrush(Colors.Transparent);
                                    //b.Margin = new Thickness(5, 5, 5, 5);
                                    //b.Content = column.ColumnTitle;
                                    //b.HorizontalAlignment = HorizontalAlignment.Center;
                                    //b.IsHitTestVisible = false;
                                    //b.Cursor = Cursors.Hand;
                                    //TextBlock tip = new TextBlock();
                                    //tip.Text = "Click to Sort by " + column.ColumnTitle;
                                    //ToolTipService.SetToolTip(b, tip);
                                    col.Header = column.ColumnTitle;
                                    col.SortMemberPath = column.ColumnName;
                                    try
                                    {
                                        r.ris.Columns.Add(col);
                                    }
                                    finally
                                    { }
                                }
                            }
                        }
                        r.ris.CanUserReorderColumns = false;
                        //r.ris.FrozenColumnCount = 2;
                        r.ris.IsReadOnly = true;
                        r.ris.HorizontalContentAlignment = HorizontalAlignment.Center;
                        r.ResizeMode = ResizeMode.CanResize;

                        //r.VerticalAlignment = VerticalAlignment.Stretch;
                        r.HorizontalAlignment = HorizontalAlignment.Center;
                        r.ParentLayoutRoot = appo;
                        r.Title = _TableSel + " Data";
                        r.Show();
                    }
                }
                else
                {
                    MessageBox.Show(e.ServiceError.Message);
                }

            }
            else
            {
                MessageBox.Show(e.Error.Message + " " + host);
            }
        }
        private void CaricaGriglia(DataSetData tb, int Table, DataGrid g )
        {
            
            foreach (nwDataBase.DataColumnInfo column in tb.Tables[Table].Columns)
            {
              
                if (column.DisplayIndex != -1)
                {
                    DataGridColumn col;
                    DataTemplate dt;
                    if (column.DataTypeName == typeof(bool).FullName)
                    {
                        DataGridCheckBoxColumn checkBoxColumn = new DataGridCheckBoxColumn();
                        checkBoxColumn.Binding = new Binding(column.ColumnName);
                        col = checkBoxColumn;
                    }
                    else if (column.DataTypeName == typeof(DateTime).FullName)
                    {
                        col = null;
                        try
                        {
                            DataGridTemplateColumn templateColumn = new DataGridTemplateColumn();
                            string temp = TemplateManager.DataTemplates["DateTimeCellTemplate"];
                            temp = temp.Replace("@HorizontalAlignment@", HorizontalAlignment.Left.ToString());
                            temp = temp.Replace("@Text@", column.ColumnName);
                            temp = temp.Replace("@DateTimeFormat@", "MM/dd/yyyy");

                            dt = XamlReader.Load(temp) as DataTemplate;
                            templateColumn.CellTemplate = dt;

                            DataTemplate t = new DataTemplate();

                            temp = TemplateManager.DataTemplates["DateTimeCellEditingTemplate"];
                            temp = temp.Replace("@HorizontalAlignment@", HorizontalAlignment.Left.ToString());
                            temp = temp.Replace("@SelectedDate@", column.ColumnName);

                            dt = XamlReader.Load(temp) as DataTemplate;

                            templateColumn.CellEditingTemplate = dt;
                            col = templateColumn;
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                    }
                    else
                    {
                        DataGridTextColumn textColumn = new DataGridTextColumn();
                        textColumn.Binding = new Binding(column.ColumnName);
                        textColumn.Binding.ValidatesOnExceptions = true;
                        col = textColumn;
                    }

                    col.IsReadOnly = column.IsReadOnly;

                    //HyperlinkButton b = new HyperlinkButton();
                    //b.Background = new SolidColorBrush(Colors.Transparent);
                    //b.Margin = new Thickness(5, 5, 5, 5);
                    //b.Content = column.ColumnTitle;
                    //b.HorizontalAlignment = HorizontalAlignment.Center;
                    //b.IsHitTestVisible = false;
                    //b.Cursor = Cursors.Hand;
                    //TextBlock tip = new TextBlock();
                    //tip.Text = "Click to Sort by " + column.ColumnTitle;
                    //ToolTipService.SetToolTip(b, tip);
                    col.Header = column.ColumnTitle;
                    col.SortMemberPath = column.ColumnName;
                    try
                    {
                        g.Columns.Add(col);
                    }
                    finally
                    {
                       
                    }
                }
            }
 
        }
        #endregion
        #region Table Object
        private void mnuTBSchema(object sender, RoutedEventArgs e)
        {
            // Get Object type and Table Name (Format Type + Table Name)
            PopupMenuItem mi = (PopupMenuItem)sender;
            string table = mi.Name.Substring(1);
            tiporichiesto = mi.Name.Substring(0, 1);
            _TableSel = table;
            // Set Parameter
            wzSqlAdmin.nwDataBase.schematype t = wzSqlAdmin.nwDataBase.schematype.Colonne;
            switch (tiporichiesto)
            {
                case "T":
                    t = wzSqlAdmin.nwDataBase.schematype.Nomi;
                    break;
                case "C":
                    t = wzSqlAdmin.nwDataBase.schematype.Colonne;
                    break;
                case "I":
                    t = wzSqlAdmin.nwDataBase.schematype.Identity;
                    break;
                case "G":
                    t = wzSqlAdmin.nwDataBase.schematype.Guid;
                    break;
                case "F":
                    t = wzSqlAdmin.nwDataBase.schematype.FileGroup;
                    break;
                case "X":
                    t = wzSqlAdmin.nwDataBase.schematype.Indici;
                    break;
                case "S":
                    t = wzSqlAdmin.nwDataBase.schematype.Constrains;
                    break;
                case "E":
                    t = wzSqlAdmin.nwDataBase.schematype.RifForeignKey;
                    break;
                case "V":
                    t = wzSqlAdmin.nwDataBase.schematype.RifView;
                    break;
                case "P":
                    t = wzSqlAdmin.nwDataBase.schematype.Procedure;
                    break;
                case "R":
                    t = wzSqlAdmin.nwDataBase.schematype.Parameters;
                    break;
                case "U":
                    t = wzSqlAdmin.nwDataBase.schematype.UserInfo;
                    break;
            }
            // Load Data
            db.getSchemaAsync(parametri, table, t, 1, 100);
            Busy.IsBusy = true;

            Acc.IsEnabled = false;
        }
        private void RecSchema(object sender, getSchemaCompletedEventArgs e)
        {
            Busy.IsBusy = false;
            Acc.IsEnabled = true;
            if (e.Error == null)
            {

                if (e.ServiceError == null)
                {
                    int tablenum = 0;
                    switch (tiporichiesto)
                    {
                        case "T":
                            tiporichiesto = " Table";
                            tablenum = 0;
                            break;
                        case "C":
                            tiporichiesto = " Columns";
                            tablenum = 1;
                            break;
                        case "I":
                            tiporichiesto = " Identities";
                            tablenum = 2;
                            break;
                        case "G":
                            tiporichiesto = " GUID";
                            tablenum = 3;
                            break;
                        case "F":
                            tiporichiesto = " Files Group";
                            tablenum = 4;
                            break;
                        case "X":
                            tiporichiesto = " Index";
                            tablenum = 5;
                            break;
                        case "S":
                            tiporichiesto = " Constrains";
                            tablenum = 6;
                            break;
                        case "E":
                            tiporichiesto = " External Reference";
                            tablenum = 7;
                            break;
                        case "V":
                            tiporichiesto = " Views Reference";
                            tablenum = 8;
                            break;
                        case "P":
                            tiporichiesto = " Procedure";
                            tablenum = 0;
                            break;
                        case "R":
                            tiporichiesto = " Parameters";
                            tablenum = 1;
                            break;
                        case "U":
                            tiporichiesto = " User Info";
                            tablenum = 0;
                            break;
                    }
                    _tables = e.Result.Tables;

                    IEnumerable list = DynamicDataBuilder.GetDataList(e.Result, 1);
                    if (e.UserState as string == "Lookup")
                        _lookup = list;
                    else
                    {
                        if (tablenum == 2)
                        {
                            pagine.TableProperty r = new pagine.TableProperty();

                            r.dati.ItemsSource = DynamicDataBuilder.GetDataList(e.Result, tablenum);
                            IEnumerable tabella = DynamicDataBuilder.GetDataList(e.Result, 0);
                            IEnumerator enu = tabella.GetEnumerator();
                            enu.MoveNext();
                            Util.DataObject dati = (Util.DataObject)enu.Current;
                            r.owner.Content = dati.GetFieldValue("Owner");
                            r.owner.Content = Thread.CurrentThread.CurrentCulture.Name;
                            DateTimeConverter d = new DateTimeConverter();
                            r.creationdate.Content = Convert.ToDateTime(dati.GetFieldValue("Created_datetime")).ToString("dd-MM-yyyy", CultureInfo.InvariantCulture);
                            tabella = DynamicDataBuilder.GetDataList(e.Result, 3);
                            enu = tabella.GetEnumerator();
                            enu.MoveNext();
                            r.rowguidcol.Content = dati.GetFieldValue("RowGuidCol");

                            r.HorizontalAlignment = HorizontalAlignment.Center;
                            r.ParentLayoutRoot = appo;
                            r.Title = _TableSel + tiporichiesto;

                            if (e.UserState != null)
                            {
                                r.Title += e.UserState.ToString();
                            }
                            r.Show();
                        }
                        else
                        {
                            IEnumerable valori = DynamicDataBuilder.GetDataList(e.Result, tablenum);
                            if (valori != null)
                            {
                                pagine.SchemaTable r = new pagine.SchemaTable();
                                r.columns.Columns.Clear();
                                r.columns.ItemsSource = valori;
                                IEnumerable tabella = DynamicDataBuilder.GetDataList(e.Result, 0);
                                IEnumerator enu = tabella.GetEnumerator();
                                Util.DataObject dati = null;
                               
                                if (enu.MoveNext())
                                {
                                    dati = (Util.DataObject)enu.Current;
                                    r.owner.Content = dati.GetFieldValue("Owner");
                                    DateTimeConverter d = new DateTimeConverter();
                                    r.creationdate.Content = Convert.ToDateTime(dati.GetFieldValue("Created_datetime")).ToString("dd-MM-yyyy", CultureInfo.InvariantCulture);
                                    if (e.Result.Tables.Count > 3)
                                    {
                                        tabella = DynamicDataBuilder.GetDataList(e.Result, 3);
                                        enu = tabella.GetEnumerator();
                                        enu.MoveNext();
                                        Util.DataObject o = (Util.DataObject)enu.Current;
                                        r.rowguidcol.Content = (string)o.GetFieldValue("RowGuidCol");
                                        if (r.rowguidcol.Content.ToString().ToLower().Contains("no rowguid"))
                                        {
                                            r.rowguidcol.Content = "";
                                        }
                                    }
                                }

                                //Colonne
                                CaricaGriglia(e.Result, tablenum, r.columns );
                           
                                r.columns.CanUserReorderColumns = false;
                                //r.columns.FrozenColumnCount = 2;
                                r.columns.HorizontalContentAlignment = HorizontalAlignment.Center;
                                r.HorizontalAlignment = HorizontalAlignment.Center;
                                r.ParentLayoutRoot = appo;
                                r.Title = _TableSel + tiporichiesto;

                                if (e.UserState != null)
                                {
                                    r.Title += e.UserState.ToString();
                                }
                                r.Show();
                            }
                            else
                            {
                                MessageBox.Show("No value");
                            }
                        }
                    }
                }
                else
                {
                    MessageBox.Show(e.ServiceError.Message);
                }
            }
            else
            {
                MessageBox.Show(e.Error.Message + " " + host);
            }
        }
    
        #endregion

        private void ExecQuery(object sender, RoutedEventArgs e)
        {

        }

        private void Install(object sender, RoutedEventArgs e)
        {
            Application.Current.Install();
        }
    }

}
